import requests
import json


import pymysql
import xlrd
import time

conn = pymysql.connect(host='192.168.2.205', port=3306, user='jinhuitao', password='jinhuitao_123456',
                       database='sunway_db', charset='utf8')
cursor = conn.cursor()


def excel_s():
    books = xlrd.open_workbook(r"D:/九方.xlsx")

    sheet = books.sheet_by_index(0)  # sheet_by_index(0)表示为ceshishujvku.xlsx中的第一个表单
    print(f"第一列的内容是:{sheet.col_values(colx=0)}")

    return sheet.col_values(colx=0)


def mysql_s(times, title, Tracking_number):
    newtime = time.strftime('%Y-%m-%d %H:%M:%S')
    cursor.execute(
        "REPLACE into load_nine set newtime=%s,times=%s,title=%s,Tracking_number=%s",
        [newtime, times, title, Tracking_number])

    conn.commit()


def run1():
    for i in excel_s():
        url = 'http://www.tofba.com:8081/seaair/waybills/listPage'
        headers = {
            'Accept': 'application/json, text/plain, */*',
            'Accept-Encoding': 'gzip, deflate',
            'Accept-Language': 'zh-CN,zh;q=0.9,en-US;q=0.8,en;q=0.7',
            'Authorization': 'eyJhbGciOiJIUzI1NiJ9.eyJyZWdpc3RlcmVkTW9iaWxlUGhvbmVOdW1iZXIiOiIxODAyODU2OTY0MCIsInN1YiI6IjM4NzgiLCJjdXN0b21lckxvZ2luTmFtZSI6IlNXRFpTVyIsImlzcyI6ImF1dGhvciIsImN1c3RvbWVyQ29kZSI6Iko0MjQ1IiwiaWQiOjM4NzgsImV4cCI6MTYyMTg0MzY0NiwiaWF0IjoxNjIxODQzNjQ2LCJqdGkiOiIyY2VjOTYwMC0yODI4LTRiMmQtODRmZS0yYTE1MTk2YjQwOTEiLCJjdXN0b21lck5hbWVTaG9ydCI6Iuebm-S4uueUteWtkOWVhuWKoSJ9.D4BRKMUuV5bf6JS6dkydn1-9Z0CjbOUbqppqhMviA8E',
            'Cache-Control': 'no-cache',
            'Content-Length': '209',
            'Content-Type': 'application/json; charset=UTF-8',
            'Host': 'www.tofba.com:8081',
            'Origin': 'http://tofba.com:8887',
            'Pragma': 'no-cache',
            'Proxy-Connection': 'keep-alive',
            'Referer': 'http://tofba.com:8887/',
            'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/92.0.4515.107 Safari/537.36',
            'x-tofba-session-status': 'y',
        }
        parm = json.dumps(
                {"status": [0], "inStockChannel": "", "waybillNo": "", "trackNumber": "", "referenceNo": "{}".format(i),
                 "countryId": "", "declareNameCn": "", "sku": "", "dispatchAddressCode": "", "shipmentID": "",
                 "pageSize": 20, "currentPage": 1})

        res=requests.post(url,headers=headers,data=parm).json()
        print(res['datas'][0]['id'])
        id=res['datas'][0]['id']
        run(i,id)


def run(i,id):
    url = 'http://www.tofba.com:8081/seaair/waybills/initWaybillState?id={}'.format(id)
    headers = {
        'Host': 'www.tofba.com:8081',
        'Connection': 'keep-alive',
        'Content-Length': '0',
        'Pragma': 'no-cache',
        'Cache-Control': 'no-cache',
        'Accept': 'application/json, text/plain, */*',
        'x-tofba-session-status': 'y',
        'Authorization': 'eyJhbGciOiJIUzI1NiJ9.eyJyZWdpc3RlcmVkTW9iaWxlUGhvbmVOdW1iZXIiOiIxODAyODU2OTY0MCIsInN1YiI6IjM4NzgiLCJjdXN0b21lckxvZ2luTmFtZSI6IlNXRFpTVyIsImlzcyI6ImF1dGhvciIsImN1c3RvbWVyQ29kZSI6Iko0MjQ1IiwiaWQiOjM4NzgsImV4cCI6MTYyMTg0MzY0NiwiaWF0IjoxNjIxODQzNjQ2LCJqdGkiOiIyY2VjOTYwMC0yODI4LTRiMmQtODRmZS0yYTE1MTk2YjQwOTEiLCJjdXN0b21lck5hbWVTaG9ydCI6Iuebm-S4uueUteWtkOWVhuWKoSJ9.D4BRKMUuV5bf6JS6dkydn1-9Z0CjbOUbqppqhMviA8E',
        'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/92.0.4515.107 Safari/537.36',
        'Origin': 'http://tofba.com:8887',
        'Referer': 'http://tofba.com:8887/',
        'Accept-Encoding': 'gzip, deflate',
        'Accept-Language': 'zh-CN,zh;q=0.9,en-US;q=0.8,en;q=0.7',
    }
    res = requests.post(url, headers=headers).json()
    # print(res['data'])
    for j in res['data']:
        print(j)
        times=j['operationDateTime']
        title=j['content']
        Tracking_number=i
        # mysql_s(times, title, Tracking_number)


if __name__ == '__main__':
    run1()
